package ImplementInterface;

import DatabaseInterface.Ad2Service;
import HibernateFacet.DataRecord;
import HibernateFacet.DeviceInformation;
import HibernateFacet.second.*;
/*import com.sun.org.apache.bcel.internal.generic.IF_ACMPEQ;*/
import com.message.source.SmsSDKDemo;
import com.message.source.SmsVoicePromptSender;
import com.message.source.SmsVoicePromptSenderResult;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.joda.time.DateTime;

import javax.sql.rowset.spi.SyncResolver;
import java.io.DataOutputStream;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Ad2ServiceImpl implements Ad2Service {
    public SessionFactory sessionFactory;
    public Session session;

    public SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    @Override
    public int addDeviceData(String addr, final DataOutputStream out, byte[] data) {
        double aE = (double) ((((int) data[14] & 0xff) << 8) + ((int) data[15] & 0xff)) / 10;
        double aV = (double) ((((int) data[16] & 0xff) << 8) + ((int) data[17] & 0xff)) / 10;
        double bE = (double) ((((int) data[18] & 0xff) << 8) + ((int) data[19] & 0xff)) / 10;
        double bV = (double) ((((int) data[20] & 0xff) << 8) + ((int) data[21] & 0xff)) / 10;
        double cE = (double) ((((int) data[22] & 0xff) << 8) + ((int) data[23] & 0xff)) / 10;
        double cV = (double) ((((int) data[24] & 0xff) << 8) + ((int) data[25] & 0xff)) / 10;
        Integer aP = ((((int) data[26] & 0xff) << 8) + ((int) data[27] & 0xff));
        Integer bP = ((((int) data[28] & 0xff) << 8) + ((int) data[29] & 0xff));
        Integer cP = ((((int) data[30] & 0xff) << 8) + ((int) data[31] & 0xff));
        Integer sumP = ((((int) data[32] & 0xff) << 8) + ((int) data[33] & 0xff));
        double aNumber = ((((int) data[34] & 0xff) << 8) + ((int) data[35] & 0xff));
        double bNumber = ((((int) data[36] & 0xff) << 8) + ((int) data[37] & 0xff));
        double cNumber = ((((int) data[38] & 0xff) << 8) + ((int) data[39] & 0xff));
        double sumNumber = ((((int) data[40] & 0xff) << 8) + ((int) data[41] & 0xff));
        Integer condition = ((int) data[42] & 0xff);
        if (condition == 0) {
            condition = 2;
        }
        System.out.println("aE=" + aE);
        System.out.println("aV=" + aV);
        System.out.println("aP=" + aP);
        System.out.println("sumP=" + sumP);
        System.out.println("aNumber=" + aNumber);
        System.out.println("sumNumber=" + sumNumber);
        System.out.println("condition=" + condition);

        DeviceData deviceData = new DeviceData();
        Electricity electricity = new Electricity();


        deviceData.setaElectricity(aE);
        //deviceData.setaElectricity(BigDecimal.valueOf(aE));
        deviceData.setaVoltage(aV);
        deviceData.setaPower(aP);

        deviceData.setbElectricity(bE);
        deviceData.setbVoltage(bV);
        deviceData.setbPower(bP);

        deviceData.setcElectricity(cE);
        deviceData.setcVoltage(cV);
        deviceData.setcPower(cP);

        deviceData.setSumPower(sumP);

        electricity.setaNumber(aNumber);
        electricity.setbNumber(bNumber);
        electricity.setcNumber(cNumber);
        electricity.setSumNumber(sumNumber);

        System.out.println(deviceData);
        System.out.println(electricity);
        /*SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String date = sdf.format(new Date());*/
        Date now = new Date();
        String dateTime = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now);

        /*String startOfDay = new SimpleDateFormat("yyyy-MM-dd 00:00:00").format(now);
        String endOfDay = new SimpleDateFormat("yyyy-MM-dd 23:59:59").format(now);*/
        System.out.println(dateTime);

        //int month = now.getMonth() + 1;
        int i = -1;
        try {
            session = sessionFactory.openSession();
          /* Device device = (Device) session.createSQLQuery("SELECT * FROM t_device WHERE number = :number")
                    .addEntity(Device.class)
                    .setParameter("number",addr)
                    .uniqueResult(); */
            Integer deviceId = (Integer) session.createSQLQuery("SELECT id FROM t_device WHERE number = :number")
                    .setParameter("number", addr)
                    .uniqueResult();
            session.close();
            System.out.println("deviceId=" + deviceId);
            session = sessionFactory.openSession();
            int ret = session.createSQLQuery("INSERT INTO t_deviceData(deviceId, aElectricity, aVoltage, bElectricity, bVoltage, cElectricity, cVoltage, aPower, bPower, cPower, sumPower, createTime) " +
                            "VALUES (:deviceId, :aElectricity, :aVoltage, :bElectricity, :bVoltage, :cElectricity, :cVoltage, :aPower, :bPower, :cPower, :sumPower, :createTime)")
                    .setParameter("deviceId", deviceId)
                    .setParameter("aElectricity", deviceData.getaElectricity())
                    .setParameter("aVoltage", deviceData.getaVoltage())
                    .setParameter("bElectricity", deviceData.getbElectricity())
                    .setParameter("bVoltage", deviceData.getbVoltage())
                    .setParameter("cElectricity", deviceData.getcElectricity())
                    .setParameter("cVoltage", deviceData.getcVoltage())
                    .setParameter("aPower", deviceData.getaPower())
                    .setParameter("bPower", deviceData.getbPower())
                    .setParameter("cPower", deviceData.getcPower())
                    .setParameter("sumPower", deviceData.getSumPower())
                    .setParameter("createTime", now)
                    .executeUpdate();
            session.close();
            if (ret != -1) {
                byte[] data2 = new byte[]{(byte) 0x03, 0x4F, 0x4B};
               /* int crc = CRC16.getCRC1021(data, data.length - 2);
                int ch = (int) (crc >> 8) & 0xff;
                int cl = (int) crc & 0xff;
                data[data.length - 2] = (byte) ch;
                data[data.length - 1] = (byte) cl;*/
                try {
                    out.write(data2);
                    out.flush();
                } catch (Exception e) {
                    e.getMessage();
                }
            }

            session = sessionFactory.openSession();
            String dateTime2 = new java.text.SimpleDateFormat("yyyy-MM-dd").format(now);
            /*List<Electricity> list = session.createSQLQuery("SELECT * FROM t_electricity WHERE deviceId = :deviceId AND DATE(createTime) = :dateTime")
                    .addEntity(Electricity.class)
                    .setParameter("deviceId", deviceData.getDeviceId())
                    .setParameter("dateTime", dateTime2)
                    .list();
             System.out.println("找到了今天的数据" + list);*/
            Electricity nowElectricity = (Electricity) session.createSQLQuery("SELECT * FROM t_electricity WHERE deviceId = :deviceId AND DATE(createTime) = :dateTime")
                    .addEntity(Electricity.class)
                    .setParameter("deviceId", deviceId)
                    .setParameter("dateTime", dateTime2)
                    .uniqueResult();
            System.out.println("找到了今天的数据" + nowElectricity);
            if (nowElectricity != null) {
                session = sessionFactory.openSession();
                int ret2 = session.createSQLQuery("update t_electricity set  " +
                                "aNumber= :aNumber, bNumber=:bNumber,cNumber= :cNumber,sumNumber= :sumNumber,updateTime= :updateTime where id = :id")
                        .setParameter("aNumber", electricity.getaNumber() + nowElectricity.getaNumber())
                        .setParameter("bNumber", electricity.getbNumber() + nowElectricity.getbNumber())
                        .setParameter("cNumber", electricity.getcNumber() + nowElectricity.getcNumber())
                        .setParameter("sumNumber", electricity.getSumNumber() + nowElectricity.getSumNumber())
                        .setParameter("updateTime", dateTime)
                        .setParameter("id", nowElectricity.getId())
                        .executeUpdate();
                session.close();
                if (ret2 == 1) {
                    System.out.println("更新今天用电数据成功");
                }
            } else {
                session = sessionFactory.openSession();
                int ret2 = session.createSQLQuery("INSERT INTO t_electricity(deviceId, aNumber, bNumber, cNumber, sumNumber, createTime) " +
                                "VALUES (:deviceId, :aNumber, :bNumber, :cNumber, :sumNumber, :createTime)")
                        .setParameter("deviceId", deviceId)
                        .setParameter("aNumber", electricity.getaNumber())
                        .setParameter("bNumber", electricity.getbNumber())
                        .setParameter("cNumber", electricity.getcNumber())
                        .setParameter("sumNumber", electricity.getSumNumber())
                        .setParameter("createTime", dateTime)
                        .executeUpdate();
                session.close();
                if (ret2 == 1) {
                    System.out.println("添加用电数据成功");
                }
            }

            session = sessionFactory.openSession();
            int ret3 = session.createSQLQuery("update t_device set `condition` = :condition ,sumPower= :sumPower where t_device.number = :number")
                    .setParameter("condition", condition)
                    .setParameter("sumPower", sumP)
                    .setParameter("number", addr).executeUpdate();
            session.close();
            if (ret3 != -1) System.out.println("修改合闸状态成功");


        } catch (Exception e) {
            e.printStackTrace();
        }

        return i;
    }
    public int PhoneNotifications(String phone,String number,String warning) {
        //打电话
        int appid = 1400036402;
        String appkey = "99407e02cf8515dcbc8a142b15d834f1";
        String message = "尊敬的用户您好！您的"+"ZKP51-63H项目中" +number+"号低压智能断路器已检测出线路中存在"+warning+"，请检修！";
        SmsVoicePromptSender smsVoicePromtSender = new SmsVoicePromptSender(appid, appkey);
        SmsVoicePromptSenderResult smsSingleVoiceSenderResult;
        try {
            smsSingleVoiceSenderResult = smsVoicePromtSender.send("86", phone, 2, 2, message, "");
            System.out.println(smsSingleVoiceSenderResult);
            return 1;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return -1;
    }
    //设备故障及操作记录
    @Override
    public int addDevice(String addr, DataOutputStream out, byte[] data) {
        Integer condition = ((int) data[14] & 0xff);
        Integer fault = ((int) data[15] & 0xff);
        if (condition == 0x00) {
            condition = 2;
        }
        String fault2 = null;
        if (fault == 1) {
            fault2 = "漏电报警";
        } else if (fault == 2) {
            fault2 = "电弧故障报警";
        }
        //故障短信通知及电话通知，真实环境打开注释
        /*if (fault2!=null) {
            SmsSDKDemo d = new SmsSDKDemo();
            session = sessionFactory.openSession();
            String phone = (String) session.createSQLQuery("select u.phone FROM t_device d,t_user u where d.number= :number and u.id = d.userId;")
                    .setParameter("number", addr)
                    .uniqueResult();
            if (!StringUtils.isBlank(phone)) {
                d.messageClient3(phone, addr,fault2);
            }
            int ret = PhoneNotifications(phone, addr, fault2);

        }*/
        String fault3 = "";
        if (condition == 0x01) {
            fault3 = "合闸";
        } else if (condition == 2) {
            fault3 = "分闸";
        }
        String cause = null;
        if (fault3.equals("分闸") && fault2 !=null) {
            cause = fault2 + "分闸";
        }else  if (fault3.equals("分闸") && fault2 == null) {
            cause = "人工分闸";
        }
        System.out.println(condition);
        System.out.println(fault2);
        Date now = new Date();
        String dateTime = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(now);
        byte[] data2 = new byte[]{(byte) 0x04, 0x4F, 0x4B};
        try {
            session = sessionFactory.openSession();
            int ret = session.createSQLQuery("update t_device set fault = :fault,`condition` = :condition where t_device.number = :number")
                    .setParameter("fault", fault2)
                    .setParameter("condition", condition)
                    .setParameter("number", addr).executeUpdate();
            if (ret == 1) {
                System.out.println("修改设备" + addr + "状态成功");
                out.write(data2);
                out.flush();
            }
            session.close();

            session = sessionFactory.openSession();
            Integer deviceId = (Integer) session.createSQLQuery("SELECT id FROM t_device WHERE number = :number")
                    .setParameter("number", addr)
                    .uniqueResult();

            if (!fault2.equals("")) {
                //故障记录
                int ret2 = session.createSQLQuery("INSERT INTO t_warning(deviceId, faultMsg, createTime) " +
                                "VALUES (:deviceId, :faultMsg, :createTime)")
                        .setParameter("deviceId", deviceId)
                        .setParameter("faultMsg", fault2)
                        .setParameter("createTime", dateTime)
                        .executeUpdate();
                session.close();
                if (ret2 == 1) {
                    System.out.println("添加故障记录成功");
                }
            }
            //分闸合闸记录  分闸合闸操作记录和故障记录存在一起
            int ret3 = session.createSQLQuery("INSERT INTO t_warning(deviceId, faultMsg, createTime) " +
                            "VALUES (:deviceId, :faultMsg, :createTime)")
                    .setParameter("deviceId", deviceId)
                    .setParameter("faultMsg", fault3)
                    .setParameter("createTime", dateTime)
                    .executeUpdate();
            session.close();
            if (ret3 == 1) {
                System.out.println("添加设备分合闸记录成功");
            }
            int ret4 = session.createSQLQuery("update t_device set cause = :cause where id = :id")
                    .setParameter("cause",cause)
                    .setParameter("id", deviceId)
                    .executeUpdate();
            if (ret4 == 1) {
                System.out.println("添加设备分闸原因成功");
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
               /* int crc = CRC16.getCRC1021(data, data.length - 2);
                int ch = (int) (crc >> 8) & 0xff;
                int cl = (int) crc & 0xff;
                data[data.length - 2] = (byte) ch;
                data[data.length - 1] = (byte) cl;*/

        return -1;
    }

    @Override
    public int updateCondition(String addr, DataOutputStream out, byte[] data) {
        Integer condition = ((int) data[14] & 0xff);
        if (condition == 32) {
            System.out.println("分闸");
            try {
                session = sessionFactory.openSession();
                int ret = session.createSQLQuery("update t_device set `condition` = :condition, cause= :cause where t_device.number = :number")
                        .setParameter("condition", 2)
                        .setParameter("cause", "远程分闸")
                        .setParameter("number", addr).executeUpdate();
                if (ret != -1) System.out.println("修改设备" + addr + "状态为分闸成功");
                session.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;
    }

    @Override
    public int updateCurrentRating(String addr, Integer current) {
        try {
            session = sessionFactory.openSession();
            int ret = session.createSQLQuery("update t_device set currentRating = :currentRating where t_device.number = :number")
                    .setParameter("currentRating", current)
                    .setParameter("number", addr).executeUpdate();
            if (ret == 1) System.out.println("修改设备" + addr + "额定电流为" + current + "成功");
            session.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public List<String> findUserDeviceNumber(Integer userId) {
        try {
            session = sessionFactory.openSession();
            List<String> numberList = session.createSQLQuery("select number from t_device where userId = :userId ")
                    .setParameter("userId", userId).list();
            session.close();
            System.out.println("jjjjjjjj=" + numberList);
            return numberList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }



    @Override
    public int checkDevicePWd(String number, String pwd) {
        try {
            session = sessionFactory.openSession();
            Integer deviceId = (Integer) session.createSQLQuery("SELECT id FROM t_device WHERE number = :number and pwd= :pwd")
                    .setParameter("number", number)
                    .setParameter("pwd", pwd)
                    .uniqueResult();
            session.close();
            if (deviceId != null) {
                return deviceId;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        return -1;
    }

    @Override
    public int updateDevicePWd(String id, String pwd) {
        try {
            int ret = session.createSQLQuery("update t_device set pwd = :pwd where id = :id")
                    .setParameter("pwd", pwd)
                    .setParameter("id", id).executeUpdate();
            session.close();
            if (ret == 1) System.out.println("修改设备密码成功");
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        return -1;
    }

    @Override
    public int findUserDeviceSumPower(int userId) {
        try {
            session = sessionFactory.openSession();
            BigDecimal TotalNumber = (BigDecimal)session.createSQLQuery("select sum(sumPower) as TotalNumber from t_device where userId = :userId")
                    .setParameter("userId", userId)
                    .uniqueResult();
            Integer DeviceSumPower = Integer.valueOf(TotalNumber.toString());
            session.close();
            if (TotalNumber != null) {
                return DeviceSumPower;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return -1;
    }

    @Override
    public Integer clearedSumPower(String number) {
        try {
            session = sessionFactory.openSession();
            int ret = session.createSQLQuery("update t_device set sumPower = null where number = :number ")
                    .setParameter("number", number)
                    .executeUpdate();
            session.close();
           return ret;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public int findUserDeviceNum(int userId) {
        try {
            session = sessionFactory.openSession();
            //BigDecimal deviceNum = (BigDecimal)session.createSQLQuery("select count(*) as deviceNum from t_device where userId = :userId")
            BigInteger deviceNum = (BigInteger)session.createSQLQuery("select count(*) as deviceNum from t_device where userId = :userId")
                    .setParameter("userId", userId)
                    .uniqueResult();
            Integer d2 = Integer.valueOf(deviceNum.toString());
            session.close();
            if (d2 != null) {
                return d2;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return -1;
    }

    @Override
    public List<ElectricityData> findUserDeviceUseElectricityByYear(int userId,String d1,String  d2) {
        //今年每个月的总用电数据
        try {
            session = sessionFactory.openSession();
            List<ElectricityData> electricityData = session.createSQLQuery("SELECT\n" +
                            "    de.userId as id, \n" +
                            "    YEAR(el.createTime) as Year, \n" +
                            "    MONTH(el.createTime) as Month, \n" +
                            "    SUM(el.aNumber) as TotalANumber, \n" +
                            "    SUM(el.bNumber) as TotalBNumber, \n" +
                            "    SUM(el.cNumber) as TotalCNumber,\n" +
                            "    SUM(el.sumNumber) as TotalSumNumber\n" +
                            "FROM t_electricity el ,t_device de\n" +
                            "WHERE de.userId= :userId and el.deviceId = de.Id and el.createTime >= :d1 AND el.createTime <= :d2\n" +
                            "GROUP BY YEAR(el.createTime), MONTH(el.createTime);\n")
                    .setResultTransformer(Transformers.aliasToBean(ElectricityData.class))
                    .setParameter("userId", userId)
                    .setParameter("d1", d1)
                    .setParameter("d2", d2)
                    .list();
            session.close();
            System.out.println(electricityData);
            return electricityData;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public ElectricityData findUserDeviceUseElectricity(int userId, String d1, String d2) {
        try {
            session = sessionFactory.openSession();
            ElectricityData electricityData =(ElectricityData) session.createSQLQuery("SELECT\n" +
                            "    de.userId as id,\n" +
                            "    SUM(el.aNumber) as TotalANumber, \n" +
                            "    SUM(el.bNumber) as TotalBNumber, \n" +
                            "    SUM(el.cNumber) as TotalCNumber,\n" +
                            "    SUM(el.sumNumber) as TotalSumNumber\n" +
                            "FROM t_electricity el ,t_device de\n" +
                            "WHERE de.userId= :userId and el.deviceId = de.Id\n" +
                            "and createTime >= :d1 AND createTime < :d2 ")
                    .setResultTransformer(Transformers.aliasToBean(ElectricityData.class))
                    .setParameter("userId", userId)
                    .setParameter("d1", d1)
                    .setParameter("d2", d2)
                    .uniqueResult();
            session.close();
            System.out.println(electricityData);
            return electricityData;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public ElectricityData findDeviceUseElectricity(int deviceId, String d1, String d2) {
        try {
            session = sessionFactory.openSession();
            ElectricityData electricityData =(ElectricityData) session.createSQLQuery("SELECT\n" +
                            "    de.number as id,\n" +
                            "    SUM(el.aNumber) as TotalANumber, \n" +
                            "    SUM(el.bNumber) as TotalBNumber, \n" +
                            "    SUM(el.cNumber) as TotalCNumber,\n" +
                            "    SUM(el.sumNumber) as TotalSumNumber\n" +
                            "FROM t_electricity el ,t_device de \n" +
                            "WHERE el.deviceId = :deviceId and el.deviceId = de.id and createTime >= :d1 AND createTime <= :d2 ")
                    .setResultTransformer(Transformers.aliasToBean(ElectricityData.class))
                    .setParameter("deviceId", deviceId)
                    .setParameter("d1", d1)
                    .setParameter("d2", d2)
                    .uniqueResult();
            session.close();
            System.out.println(electricityData);
            return electricityData;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    @Override
    public Integer findFaultNum(int userId) {
        try {
            session = sessionFactory.openSession();
            BigInteger count =(BigInteger) session.createSQLQuery("SELECT COUNT(*) as count\n" +
                            "FROM t_device\n" +
                            "WHERE userId = :userId AND fault IS NOT NULL;")
                    .setParameter("userId", userId)
                    .uniqueResult();
            session.close();
            int faultNum = Integer.parseInt(count.toString());
            return faultNum;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public Warning findOneWarning(int userId) {
        try {
            session = sessionFactory.openSession();
            Warning warning =(Warning) session.createSQLQuery("select d.`name`,d.number, w.faultMsg,w.createTime from t_warning w,t_device d \n" +
                            "WHERE d.id = w.deviceId and d.userId= :userId \n" +
                            "ORDER BY w.createTime DESC limit 1")
                    .setResultTransformer(Transformers.aliasToBean(Warning.class))
                    .setParameter("userId", userId)
                    .uniqueResult();
            session.close();
            System.out.println(warning);
            return warning;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Warning findDeviceWarning(int deviceId) {
        try {
            session = sessionFactory.openSession();
            Warning warning =(Warning) session.createSQLQuery("select id,deviceId,faultMsg,createTime from t_warning\n" +
                            "WHERE deviceId = :deviceId\n" +
                            "ORDER BY createTime DESC LIMIT 1")
                    .setResultTransformer(Transformers.aliasToBean(Warning.class))
                    .setParameter("deviceId", deviceId)
                    .uniqueResult();
            session.close();
            System.out.println(warning);
            return warning;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Warning> findUserWarning(int userId,String d1,String d2) {
        try {
            session = sessionFactory.openSession();
            List<Warning> warning =session.createSQLQuery("select d.`name`,d.number, w.faultMsg,w.createTime from t_warning w,t_device d \n" +
                            "WHERE d.id = w.deviceId and d.userId= :userId and w.createTime >= :d1 AND w.createTime <= :d2 \n" +
                            "ORDER BY w.createTime DESC")
                    .setResultTransformer(Transformers.aliasToBean(Warning.class))
                    .setParameter("userId", userId)
                    .setParameter("d1", d1)
                    .setParameter("d2", d2)
                    .list();
            session.close();
            System.out.println(warning);
            return warning;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Warning> findDeviceWarnings(int deviceId, String d1, String d2) {
        try {
            session = sessionFactory.openSession();
            List<Warning> warning =session.createSQLQuery("select w.id,d.number, w.faultMsg,w.createTime from t_warning w,t_device d \n" +
                            "WHERE d.id = w.deviceId and w.deviceId= :deviceId and w.createTime >= :d1 AND w.createTime <= :d2\n" +
                            "ORDER BY w.createTime DESC ")
                    .setResultTransformer(Transformers.aliasToBean(Warning.class))
                    .setParameter("deviceId",deviceId)
                    .setParameter("d1", d1)
                    .setParameter("d2", d2)
                    .list();
            session.close();
            System.out.println(warning);
            return warning;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Device> findDeviceByUserId(int userId) {
        try {
            session = sessionFactory.openSession();
            List<Device> devices =session.createSQLQuery("select id,name,number from t_device where userId = :userId")
                    .setResultTransformer(Transformers.aliasToBean(Device.class))
                    .setParameter("userId", userId)
                    .list();
            session.close();
            System.out.println(devices);
            System.out.println("找到" + devices.size());
            return devices;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Device findDeviceInfoById(int id) {
        try {
            session = sessionFactory.openSession();
            Device device = (Device) session.createSQLQuery("select id,name,number,currentRating,`condition`,cause from t_device where id= :id")
                    .setResultTransformer(Transformers.aliasToBean(Device.class))
                    .setParameter("id", id)
                    .uniqueResult();
            session.close();
            System.out.println(device);
            return device;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public DeviceData findDeviceData(int deviceId) {
        try {
            session = sessionFactory.openSession();
            DeviceData deviceData = (DeviceData) session.createSQLQuery("select * from t_deviceData WHERE deviceId = :deviceId ORDER BY createTime DESC LIMIT 1")
                    //.setResultTransformer(Transformers.aliasToBean(DeviceData.class))
                    .addEntity(DeviceData.class)
                    .setParameter("deviceId", deviceId)
                    .uniqueResult();
            session.close();
            System.out.println(deviceData);
            return deviceData;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int addDevice(Integer userId,String number) {
        try {
            session = sessionFactory.openSession();
            int ret = session.createSQLQuery("insert into t_device(userId,number) values(:userId , :number);")
                    .setParameter("userId", userId)
                    .setParameter("number", number).executeUpdate();
            session.close();
            return ret;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public int registered(String name, String pwd, String phone) {
        try {
            session = sessionFactory.openSession();
            int ret = session.createSQLQuery("insert into t_user(name,password,phone) values(:name , :password , :phone);")
                    .setParameter("name", name)
                    .setParameter("password", pwd)
                    .setParameter("phone", phone).executeUpdate();
            session.close();
            return ret;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public User login(String name, String pwd) {
        try {
            session = sessionFactory.openSession();
            User user = (User) session.createSQLQuery("select id,name,phone from t_user WHERE name = :name AND password = :password ")
                    .setResultTransformer(Transformers.aliasToBean(User.class))
                    //.addEntity(User.class)
                    .setParameter("name", name)
                    .setParameter("password", pwd)
                    .uniqueResult();
            session.close();
            return user;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public String findUserPhoneByName(String name) {
        try {
            session = sessionFactory.openSession();
            String phone = (String) session.createSQLQuery("select phone from t_user WHERE name = :name ")
                    .setParameter("name", name)
                    .uniqueResult();
            session.close();
            return phone;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateUserPwd(String name, String pwd) {
        try {
            session = sessionFactory.openSession();
            int ret = session.createSQLQuery("update t_user set password = :password where name = :name ")
                    .setParameter("password", pwd)
                    .setParameter("name", name)
                    .executeUpdate();
            session.close();
            return ret;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }
}
